﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Configuration;
using System.Data.Odbc;
using System.Data;

namespace CVSA.Suporte.Sinacor.BMFBovespa.ConsultaCorretoras
{
    public partial class AtuCorret : System.Web.UI.Page
    {
        private OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["connString"].ToString());

        protected void Page_Load(object sender, EventArgs e)
        {
            // apagar cocosistcorret
            string comando = string.Empty;
            OracleCommand cmd = new OracleCommand();
            
            

            // abrir excel
            string strConnExcel = @"Provider=Microsoft.Jet.OleDb.4.0;data source=C:\a.xls;Extended Properties=Excel 8.0";
            strConnExcel = @"Dsn=Excel Files;dbq=c:\a.xls";

            string strExcel = "SELECT * FROM [Sheet1$]";
            OdbcConnection connExcel = new OdbcConnection(strConnExcel);
            OdbcDataAdapter daex = new OdbcDataAdapter(strExcel, connExcel);
            
            DataTable odtEx = new DataTable();
            daex.Fill(odtEx);
            
            
            // loop 
            foreach (DataRow row in odtEx.Rows)
            {
                // remover existente;
                conn.Open();
                comando = "delete from cacosistcorret where cd_corret = " + row["COD"].ToString();
                cmd = new OracleCommand(comando, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }

            foreach (DataRow row in odtEx.Rows)
            {
                // // verificar se existe
                //conn.Open();
                //comando = "select 1  from cacosistcorret where cd_corret = " + row["COD"].ToString();
                //cmd = new OracleCommand(comando, conn);
                //OracleDataReader dr = cmd.ExecuteReader();
                //dr.Read();
                
                //if (! dr.HasRows)
                //{
                //    // INSERIR
                //    conn.Close();
                    conn.Open();
                    comando = "INSERT INTO cacosistcorret(CD_CORRET, CD_SISTEMA, VS_CORRET, CD_SITUACAO) VALUES (" + row["cod"].ToString() + "," + row["modulo"].ToString() + "," + "' '" + ",6)";
                    cmd = new OracleCommand(comando, conn);

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        string comm = string.Empty ;
                        comm = comando;
                        Response.Write(comando + "<br />");

                    }
                    conn.Close();
               // }
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            connExcel.Close();
        }
    }
}
